package org.longhua.replace;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.longhua.common.util.ExcelToHtmlConverter;
import org.longhua.mode.BankData;
import org.w3c.dom.Document;

public class BankExcelDownload extends HttpServlet {
	public static final String FILE_SEPARATOR = System.getProperties()
			.getProperty("file.separator");

	@Override
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String docsPath = request.getSession().getServletContext()
				.getRealPath("docs");
		String templateName = "temple2.xls";// Excel模版
		String templatePath = docsPath + FILE_SEPARATOR + templateName;
		String fileName = "allBankReport_" + System.currentTimeMillis()
				+ ".xls";// 导出Excel文件名
		String filePath = docsPath + FILE_SEPARATOR + fileName;
		List<BankData> datas = new ArrayList<BankData>();

		for (int i = 0; i < 5; i++) {
			Random rd = new Random();
			int number;
			number = Math.abs(rd.nextInt() % 51);

			BankData vo1 = new BankData();
			// 监控量
			vo1.setMonitoring(12306 + number);
			// 预警量
			vo1.setWarning(3600 + number);
			// 下发量
			vo1.setSendDown(7000 + number);
			// 按时处置回复量
			vo1.setReplyNormal(5420 + number);
			// 驳回量
			vo1.setReject(1010 + number);
			// 确认违规量
			vo1.setViolatCount(3010 + number);
			// 处罚量
			vo1.setPunish(3000 + number);
			// 整改量
			vo1.setUpdate(2510 + number);
			// 违规人数
			vo1.setViolatorCount(1020 + number);
			// 问责处罚人数
			vo1.setPunishPeo(360 + number);
			// 员工违规积分
			vo1.setEmployeeIntegral(3000 + number);
			// 机构风险扣分
			vo1.setOrganizationIntegral(5000 + number);
			// 风险积分名次
			vo1.setRankingIntegral(1);
			datas.add(vo1);

		}

		replaceModel(datas, templatePath, filePath);
		download(filePath, response);
	}

	public static boolean replaceModel(List<BankData> datas,
			String sourceFilePath, String targetFilePath) {
		boolean bool = true;
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					sourceFilePath));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);

			Integer tolmonitoring = 0;
			// 预警量 tol
			Integer tolwarning = 0;
			// 下发量 tol
			Integer tolsendDown = 0;
			// 按时处置回复量 tol
			Integer tolreplyNormal = 0;
			// 驳回量 tol
			Integer tolreject = 0;
			// 确认违规量 tol
			Integer tolviolatCount = 0;
			// 处罚量 tol
			Integer tolpunish = 0;
			// 整改量 tol
			Integer tolupdate = 0;
			// 违规人数 tol
			Integer tolviolatorCount = 0;
			// 问责处罚人数 tol
			Integer tolpunishPeo = 0;
			// 员工违规积分 tol
			Integer tolemployeeIntegral = 0;
			// 机构风险扣分 tol
			Integer tolorganizationIntegral = 0;
			// 风险积分名次 tol
			Integer tolrankingIntegral = 0;

			int j = 0;
			int newrow = 2;
			for (int i = 0; i < datas.size(); i++) {
				// 去除head部分
				HSSFRow row = sheet.getRow(newrow);
				row.createCell(2).setCellValue(datas.get(i).getMonitoring());
				row.createCell(3).setCellValue(datas.get(i).getWarning());
				row.createCell(4).setCellValue(datas.get(i).getSendDown());
				row.createCell(5).setCellValue(datas.get(i).getReplyNormal());
				row.createCell(6).setCellValue(datas.get(i).getReject());
				row.createCell(7).setCellValue(datas.get(i).getViolatCount());
				row.createCell(8).setCellValue(datas.get(i).getPunish());
				row.createCell(9).setCellValue(datas.get(i).getUpdate());
				row.createCell(10)
						.setCellValue(datas.get(i).getViolatorCount());
				row.createCell(11).setCellValue(datas.get(i).getPunishPeo());
				row.createCell(12).setCellValue(
						datas.get(i).getEmployeeIntegral());
				row.createCell(13).setCellValue(
						datas.get(i).getOrganizationIntegral());
				row.createCell(14).setCellValue(
						datas.get(i).getRankingIntegral());
				newrow = newrow + 3;

				// 总笔数
				tolmonitoring = tolmonitoring + datas.get(i).getMonitoring();
				tolwarning = tolwarning + datas.get(i).getWarning();
				tolsendDown = tolsendDown + datas.get(i).getSendDown();
				tolreplyNormal = tolreplyNormal + datas.get(i).getReplyNormal();
				tolreject = tolreject + datas.get(i).getReject();
				tolviolatCount = tolviolatCount + datas.get(i).getViolatCount();
				tolpunish = tolpunish + datas.get(i).getPunish();
				tolupdate = tolupdate + datas.get(i).getUpdate();
				tolviolatorCount = tolviolatorCount
						+ datas.get(i).getViolatorCount();
				tolpunishPeo = tolpunishPeo + datas.get(i).getPunishPeo();
				tolemployeeIntegral = tolemployeeIntegral
						+ datas.get(i).getEmployeeIntegral();
				tolorganizationIntegral = tolorganizationIntegral
						+ datas.get(i).getOrganizationIntegral();
				// tolrankingIntegral = tolrankingIntegral +
				// datas.get(i).getRankingIntegral();
			}
			HSSFRow row = sheet.getRow(newrow);
			row.createCell(2).setCellValue(tolmonitoring);
			row.createCell(3).setCellValue(tolwarning);
			row.createCell(4).setCellValue(tolsendDown);
			row.createCell(5).setCellValue(tolreplyNormal);
			row.createCell(6).setCellValue(tolreject);
			row.createCell(7).setCellValue(tolviolatCount);
			row.createCell(8).setCellValue(tolpunish);
			row.createCell(9).setCellValue(tolupdate);
			row.createCell(10).setCellValue(tolviolatorCount);
			row.createCell(11).setCellValue(tolpunishPeo);
			row.createCell(12).setCellValue(tolemployeeIntegral);
			row.createCell(13).setCellValue(tolorganizationIntegral);
			// row.createCell(14).setCellValue(tolrankingIntegral);
			// 输出文件
			mergerData(wb,0,0,2);
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
	}
	
	/*public static <T> boolean replaceModel2(List<T> datas,
			String sourceFilePath, String targetFilePath) {
		boolean bool = true;
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					sourceFilePath));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);
			int j = 0;
			int newrow = 2;
			for (int i = 0; i < datas.size(); i++) {
				// 去除head部分
				HSSFRow row = sheet.getRow(newrow);
				if(i==3){
					row.createCell(0).setCellValue("分行");
				}else{
					row.createCell(0).setCellValue("石家庄分行");
				}
				
				row.createCell(2).setCellValue(datas.get(i).getMonitoring());
				row.createCell(3).setCellValue(datas.get(i).getWarning());
				row.createCell(4).setCellValue(datas.get(i).getSendDown());
				row.createCell(5).setCellValue(datas.get(i).getReplyNormal());
				row.createCell(6).setCellValue(datas.get(i).getReject());
				row.createCell(7).setCellValue(datas.get(i).getViolatCount());
				row.createCell(8).setCellValue(datas.get(i).getPunish());
				row.createCell(9).setCellValue(datas.get(i).getUpdate());
				row.createCell(10)
						.setCellValue(datas.get(i).getViolatorCount());
				row.createCell(11).setCellValue(datas.get(i).getPunishPeo());
				row.createCell(12).setCellValue(
						datas.get(i).getEmployeeIntegral());
				row.createCell(13).setCellValue(
						datas.get(i).getOrganizationIntegral());
				row.createCell(14).setCellValue(
						datas.get(i).getRankingIntegral());
				newrow = newrow + 3;

				// 总笔数
				tolmonitoring = tolmonitoring + datas.get(i).getMonitoring();
				tolwarning = tolwarning + datas.get(i).getWarning();
				tolsendDown = tolsendDown + datas.get(i).getSendDown();
				tolreplyNormal = tolreplyNormal + datas.get(i).getReplyNormal();
				tolreject = tolreject + datas.get(i).getReject();
				tolviolatCount = tolviolatCount + datas.get(i).getViolatCount();
				tolpunish = tolpunish + datas.get(i).getPunish();
				tolupdate = tolupdate + datas.get(i).getUpdate();
				tolviolatorCount = tolviolatorCount
						+ datas.get(i).getViolatorCount();
				tolpunishPeo = tolpunishPeo + datas.get(i).getPunishPeo();
				tolemployeeIntegral = tolemployeeIntegral
						+ datas.get(i).getEmployeeIntegral();
				tolorganizationIntegral = tolorganizationIntegral
						+ datas.get(i).getOrganizationIntegral();
				// tolrankingIntegral = tolrankingIntegral +
				// datas.get(i).getRankingIntegral();
			}
			HSSFRow row = sheet.getRow(newrow);
			row.createCell(2).setCellValue(tolmonitoring);
			row.createCell(3).setCellValue(tolwarning);
			row.createCell(4).setCellValue(tolsendDown);
			row.createCell(5).setCellValue(tolreplyNormal);
			row.createCell(6).setCellValue(tolreject);
			row.createCell(7).setCellValue(tolviolatCount);
			row.createCell(8).setCellValue(tolpunish);
			row.createCell(9).setCellValue(tolupdate);
			row.createCell(10).setCellValue(tolviolatorCount);
			row.createCell(11).setCellValue(tolpunishPeo);
			row.createCell(12).setCellValue(tolemployeeIntegral);
			row.createCell(13).setCellValue(tolorganizationIntegral);
			// row.createCell(14).setCellValue(tolrankingIntegral);
			
			
	/*		mergerData(wb,0,0,2);
			
			
			// 输出文件
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
		
	}*/
	
	
	
	

	private void download(String path, HttpServletResponse response) {
		try {
			// path是指欲下载的文件的路径。
			File file = new File(path);
			// 取得文件名。
			String filename = file.getName();
			// 以流的形式下载文件。
			InputStream fis = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);
			fis.close();
			// 清空response
			response.reset();
			// 设置response的Header
			response.addHeader("Content-Disposition", "attachment;filename="
					+ new String(filename.getBytes()));
			response.addHeader("Content-Length", "" + file.length());
			OutputStream toClient = new BufferedOutputStream(
					response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=gb2312");
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
	
	
	private void excelToHtml(HSSFWorkbook excelBook) throws Exception{
		
		ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter (DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument() ); 
		   excelToHtmlConverter.setOutputColumnHeaders(false); 
		   excelToHtmlConverter.setOutputRowNumbers(false); 
		    
		  excelToHtmlConverter.processWorkbook(excelBook); 
		     Document htmlDocument = excelToHtmlConverter.getDocument(); 
		     ByteArrayOutputStream outStream = new ByteArrayOutputStream(); 
		     DOMSource domSource = new DOMSource (htmlDocument); 
		     StreamResult streamResult = new StreamResult (outStream); 
		     TransformerFactory tf = TransformerFactory.newInstance(); 
		     Transformer serializer = tf.newTransformer(); 
		     serializer.setOutputProperty (OutputKeys.ENCODING, "gb2312"); 
		     serializer.setOutputProperty (OutputKeys.INDENT, "yes"); 
		     serializer.setOutputProperty (OutputKeys.METHOD, "html"); 
		     serializer.transform (domSource, streamResult); 
		     outStream.close();
	}
	
	private static HSSFWorkbook mergerData(HSSFWorkbook workbook, int sheetName, int colIndex, int startRowIndex) {
		HSSFSheet sheet = workbook.getSheetAt(sheetName);
		int lastRowNum = sheet.getLastRowNum();

		for (int i = startRowIndex; i <= lastRowNum; i++) {
		HSSFCell cell = sheet.getRow(i).getCell(colIndex);

		for (int j = i + 1; j <= lastRowNum; j++) {
		HSSFCell celltemp = sheet.getRow(j).getCell(colIndex);

		// 如果下一行与被比较行相等，则继续该循环，直到不等才跳出
		if (!celltemp.getStringCellValue().equals(cell.getStringCellValue())) {
		if (j - 1 > i) {
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(i, j - 1, colIndex, colIndex));
		}
		i = j;
		break;
		}
		if(j == lastRowNum){
		sheet.addMergedRegion(new CellRangeAddress(i, lastRowNum, colIndex, colIndex));
		}
		}
		}

		return workbook;
		}
	
	
	
	
	
}